using System;
using System.Collections.Generic;
using System.Data.SQLite;

using DataLite;
using DataLite.Values;

namespace DataLite.Data
{
    public class SqliteMasterDAO
    {
        #region Table name and table columns names
        /// <summary>
        /// Table name
        /// </summary>
        public const string TableName = "sqlite_master";
        /// <summary>
        /// Rowid column name
        /// </summary>
        public const string Rowid = "rowid";                
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Type = "type";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Name = "name";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string TblName = "tbl_name";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Rootpage = "rootpage";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Sql = "sql";

        #endregion             

        #region Parameters names
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string RowidParam = "@rowid"; 
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string TypeParam = "@type";
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string NameParam = "@name";
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string TblNameParam = "@tbl_name";
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string RootpageParam = "@rootpage";
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string SqlParam = "@sql";

        #endregion

        #region Sql comands
        
        public const string sqlSelectAll = "SELECT rowid, type, name, tbl_name, rootpage, sql FROM sqlite_master";
        public const string sqlSelectByName = "SELECT rowid, type, name, tbl_name, rootpage, sql FROM sqlite_master WHERE name = @name;";
        public const string sqlSelectByType = "SELECT rowid, type, name, tbl_name, rootpage, sql FROM sqlite_master WHERE type = @type;";
        public const string sqlCount = "SELECT count(*) FROM sqlite_master";       

        #endregion

        #region Singleton Instance

        private static SqliteMasterDAO instance = new SqliteMasterDAO();

        private SqliteMasterDAO() { }
        
        public static SqliteMasterDAO getInstance() { return instance; }

        #endregion           

        #region ISqliteMaster Members             


        public List<SqliteMasterVO> SelectAll( string connectionString )
        {
            DataHelper dataHelper = new DataHelper(connectionString);
            List<SqliteMasterVO> sqliteMasterVOList = SelectAll(dataHelper);
            dataHelper.Dispose();
            return sqliteMasterVOList;
        }

        public List<SqliteMasterVO> SelectAll(DataHelper dataHelper)
        {            
            List<SqliteMasterVO> sqliteMasterVOList = new List<SqliteMasterVO>();

            using (SQLiteDataReader dataReader = dataHelper.ExecuteReader(sqlSelectAll, null))
            {                
                while (dataReader.Read())
                {
                    SqliteMasterVO sqliteMasterVO = MakeVO(dataReader, dataHelper);                    
                    sqliteMasterVOList.Add(sqliteMasterVO);
                }                
            }
            return sqliteMasterVOList;
        }

        public List<SqliteMasterVO> SelectByName(string name, string connectionString)
        {
            DataHelper dataHelper = new DataHelper(connectionString);
            List<SqliteMasterVO> sqliteMasterVOList = SelectByName(name, dataHelper);
            dataHelper.Dispose();
            return sqliteMasterVOList;
        }

        public List<SqliteMasterVO> SelectByName(string name, DataHelper dataHelper)
        {
            List<SqliteMasterVO> sqliteMasterVOList = new List<SqliteMasterVO>();

            SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter(NameParam, name) };

            using (SQLiteDataReader dataReader = dataHelper.ExecuteReader(sqlSelectByName, parameters))
            {
                while (dataReader.Read())
                {
                    SqliteMasterVO sqliteMasterVO = MakeVO(dataReader, dataHelper);
                    sqliteMasterVOList.Add(sqliteMasterVO);
                }
            }
            return sqliteMasterVOList;
        }

        public List<SqliteMasterVO> SelectByType(string type, string connectionString)
        {
            DataHelper dataHelper = new DataHelper(connectionString);
            List<SqliteMasterVO> sqliteMasterVOList = SelectByType(type, dataHelper);
            dataHelper.Dispose();
            return sqliteMasterVOList;
        }

        public List<SqliteMasterVO> SelectByType(string type, DataHelper dataHelper)
        {
            List<SqliteMasterVO> sqliteMasterVOList = new List<SqliteMasterVO>();

            SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter(TypeParam, type) };

            using (SQLiteDataReader dataReader = dataHelper.ExecuteReader(sqlSelectByType, parameters))
            {
                while (dataReader.Read())
                {
                    SqliteMasterVO sqliteMasterVO = MakeVO(dataReader, dataHelper);
                    sqliteMasterVOList.Add(sqliteMasterVO);
                }
            }
            return sqliteMasterVOList;
        }

        public SqliteMasterVO MakeVO(SQLiteDataReader dataReader, DataHelper dataHelper)
        {
            SqliteMasterVO sqliteMasterVO = new SqliteMasterVO();
            sqliteMasterVO.RowId = dataHelper.GetInt32(dataReader, Rowid, 0);
            sqliteMasterVO.Type = dataHelper.GetString(dataReader, Type, String.Empty);
            sqliteMasterVO.Name = dataHelper.GetString(dataReader, Name, String.Empty);
            sqliteMasterVO.TblName = dataHelper.GetString(dataReader, TblName, String.Empty);
            sqliteMasterVO.RootPage = dataHelper.GetInt32(dataReader, Rootpage, 0);
            sqliteMasterVO.Sql = dataHelper.GetString(dataReader, Sql, String.Empty);
            return sqliteMasterVO;
        }

        #endregion
    }
}
